create external table jms_dm.dm_all_transfer_count_deatil_dt
(
date_time date COMMENT '时间dt' ,
waybill_no string COMMENT '单号' ,
taking_code      string COMMENT '揽收网点code' ,
taking_name string COMMENT '揽收网点name' ,
taking_network_type  string COMMENT '揽收网点类型，网点类型:1总部,2代理区,3加盟商,4中心,5集散点,6网点' ,
taking_fran_code   string COMMENT '揽收网点加盟商' ,
taking_fran_name    string COMMENT '揽收网点加盟商code' ,
taking_agent_code   string COMMENT '代理区编码' ,
taking_agent_name    string COMMENT '代理区名称',
taking_manage_code   string COMMENT '管理大区名称',
taking_manage_name    string COMMENT '管理大区名称',
taking_provider_id     string COMMENT '省份ID',
taking_provider_desc  string COMMENT '省份',
taking_city_id         string COMMENT '城市ID',
taking_city_desc    string COMMENT '城市',
taking_area_id     string COMMENT '区/县ID',
taking_area_desc    string COMMENT '区/县',
taking_time       timestamp COMMENT '揽收时间',
first_center_network_code  string COMMENT '始发中心' ,
first_center_network_name string COMMENT '始发中心name' ,
end_center_network_code  string COMMENT '目的中心' ,
end_center_network_name string COMMENT '目的中心name' ,
sign_network_code  string COMMENT '签收网点' ,
sign_network_name   string COMMENT '签收网点name' ,
sign_network_type   string COMMENT '签收网点类型' ,
sign_fran_code   string COMMENT '签收网点加盟商' ,
sign_fran_name    string COMMENT '签收网点加盟商code' ,
sign_agent_code   string COMMENT '代理区编码' ,
sign_agent_name    string COMMENT '代理区名称',
sign_manage_code   string COMMENT '管理大区名称',
sign_manage_name    string COMMENT '管理大区名称',
sign_provider_id     string COMMENT '省份ID',
sign_provider_desc  string COMMENT '省份',
sign_city_id         string COMMENT '城市ID',
sign_city_desc    string COMMENT '城市',
sign_area_id     string COMMENT '区/县ID',
sign_area_desc    string COMMENT '区/县',
sign_time       timestamp COMMENT '签收时间',
terminal_dispatch_code  string COMMENT '原三段码' ,
terminal_network_code  string COMMENT '原目的地' ,
terminal_network_name string COMMENT '原目的地' ,
sj_route   string COMMENT '实际路由' ,
sj_route_simple_name   string COMMENT '实际路由简称' ,
sj_route_num bigint COMMENT '实际路由中转次数' ,
lianxu_name  string COMMENT '连续出现的路由' ,
lianxu_name_num bigint COMMENT '连续出现的路由中转次数' ,
proble_type_subject_code  string COMMENT '问题件类型' ,
proble_type_subject_name  string COMMENT '问题件类型name' ,
sj_agent_in_count bigint COMMENT '实际路由代理区内中转次数' ,
sj_agent_out_count bigint COMMENT '实际路由代理区外中转次数' ,
gh_route string COMMENT '规划路由' ,
gh_route_num bigint COMMENT '规划路由中转次数' ,
is_rational_flg bigint COMMENT '路由是否不合理，0合理，1不合理',
is_reback_flg bigint COMMENT   '是否退回件，0否，1是',
is_proble_flg bigint COMMENT   '是否问题件，0否，1是'


)
comment '全程中转次数报表'
PARTITIONED BY ( dt string COMMENT '时间分区')
stored as parquet
LOCATION '/dw/hive/jms_dm.db/external/dm_all_transfer_count_deatil_dt'
TBLPROPERTIES (     'discover.partitions' = 'false',  'parquet.column.index.access' = 'true');



create external table jms_dim.dim_all_transfer_count_proble
(
proble_name string COMMENT '问题件名称' ,
proble_code  string COMMENT '问题件code'
)
comment '全程中转次数报表-问题件维度表'
stored as parquet
LOCATION '/dw/hive/jms_dim.db/external/dim_all_transfer_count_proble'
TBLPROPERTIES (     'discover.partitions' = 'false',  'parquet.column.index.access' = 'true');

insert overwrite table jms_dim.dim_all_transfer_count_proble
select '订单调派错误'  as proble_name ,      4  as proble_code union all
select '抬头错'    as proble_name,          11 as proble_code union all
select '地址信息不详/错误'  as proble_name,   13 as proble_code union all
select '更改派送地址'     as proble_name,    15 as proble_code union all
select '运单信息不全'     as proble_name,    26 as proble_code union all
select '异常拦截件'      as proble_name ,    28 as proble_code union all
select '一货/包多单'      as proble_name,    37 as proble_code union all
select '电子运单模板不规范'  as proble_name,  58 as proble_code union all
select '不可抗拒因素'   as proble_name,      66 as proble_code union all
select '疑似刷单件'    as proble_name,       68 as proble_code



create  table jms_dm.dm_all_transfer_count_deatil_dt(
date_time date COMMENT '时间dt' ,
waybill_no varchar(100) COMMENT '单号' ,
taking_code      varchar(100) COMMENT '揽收网点code' ,
taking_name varchar(100) COMMENT '揽收网点name' ,
taking_network_type  varchar(100) COMMENT '揽收网点类型，网点类型:1总部,2代理区,3加盟商,4中心,5集散点,6网点' ,
taking_fran_code   varchar(100) COMMENT '揽收网点加盟商' ,
taking_fran_name    varchar(100) COMMENT '揽收网点加盟商code' ,
taking_agent_code   varchar(100) COMMENT '代理区编码' ,
taking_agent_name    varchar(100) COMMENT '代理区名称',
taking_manage_code   varchar(100) COMMENT '管理大区名称',
taking_manage_name    varchar(100) COMMENT '管理大区名称',
taking_provider_id     varchar(100) COMMENT '省份ID',
taking_provider_desc  varchar(100) COMMENT '省份',
taking_city_id         varchar(100) COMMENT '城市ID',
taking_city_desc    varchar(100) COMMENT '城市',
taking_area_id     varchar(100) COMMENT '区/县ID',
taking_area_desc    varchar(100) COMMENT '区/县',
taking_time       datetime COMMENT '揽收时间',
first_center_network_code  varchar(100) COMMENT '始发中心' ,
first_center_network_name varchar(100) COMMENT '始发中心name' ,
end_center_network_code  varchar(100) COMMENT '目的中心' ,
end_center_network_name varchar(100) COMMENT '目的中心name' ,
sign_network_code  varchar(100) COMMENT '签收网点' ,
sign_network_name   varchar(100) COMMENT '签收网点name' ,
sign_network_type   varchar(100) COMMENT '签收网点类型' ,
sign_fran_code   varchar(100) COMMENT '签收网点加盟商' ,
sign_fran_name    varchar(100) COMMENT '签收网点加盟商code' ,
sign_agent_code   varchar(100) COMMENT '代理区编码' ,
sign_agent_name    varchar(100) COMMENT '代理区名称',
sign_manage_code   varchar(100) COMMENT '管理大区名称',
sign_manage_name    varchar(100) COMMENT '管理大区名称',
sign_provider_id     varchar(100) COMMENT '省份ID',
sign_provider_desc  varchar(100) COMMENT '省份',
sign_city_id         varchar(100) COMMENT '城市ID',
sign_city_desc    varchar(100) COMMENT '城市',
sign_area_id     varchar(100) COMMENT '区/县ID',
sign_area_desc    varchar(100) COMMENT '区/县',
sign_time       datetime COMMENT '签收时间',
terminal_dispatch_code  varchar(100) COMMENT '原三段码' ,
terminal_network_code  varchar(100) COMMENT '原目的地' ,
terminal_network_name varchar(100) COMMENT '原目的地' ,
sj_route   varchar(500) COMMENT '实际路由' ,
sj_route_simple_name   varchar(500) COMMENT '实际路由简称' ,
sj_route_num bigint COMMENT '实际路由中转次数' ,
lianxu_name  varchar(500) COMMENT '连续出现的路由' ,
lianxu_name_num bigint COMMENT '连续出现的路由中转次数' ,
proble_type_subject_code  varchar(100) COMMENT '问题件类型' ,
proble_type_subject_name  varchar(100) COMMENT '问题件类型name' ,
sj_agent_in_count bigint COMMENT '实际路由代理区内中转次数' ,
sj_agent_out_count bigint COMMENT '实际路由代理区外中转次数' ,
gh_route varchar(500) COMMENT '规划路由' ,
gh_route_num bigint COMMENT '规划路由中转次数' ,
is_rational_flg bigint COMMENT '路由是否不合理，0合理，1不合理',
is_reback_flg bigint COMMENT   '是否退回件，0否，1是',
is_proble_flg bigint COMMENT   '是否问题件，0否，1是'
)
ENGINE = OLAP
DUPLICATE KEY( date_time,waybill_no,taking_code)
COMMENT "全程中转次数报表"
PARTITION BY RANGE(date_time)
(START ("2022-07-31") END ("2022-08-25") EVERY (INTERVAL 1 day)
)
DISTRIBUTED BY HASH(`waybill_no`) BUCKETS 4
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-180",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "4",
"in_memory" = "false",
"storage_format" = "V2"
);


alter table bidefault.dm_all_transfer_count_deatil_dt add columns(is_wrong_flg bigint comment '是否错发，0否，1是') cascade


alter table bidefault.dm_all_transfer_count_deatil_dt add columns(bak_route string comment '备用路由') cascade
alter table bidefault.dm_all_transfer_count_deatil_dt add columns(bak_route_num bigint comment '备用路由次数') cascade
alter table bidefault.dm_all_transfer_count_deatil_dt add columns(is_bak_route bigint comment '是否备用路由，0否，1是') cascade


Alter table bidefault.dm_all_transfer_count_deatil_dt change column bak_route bak_route string;



alter table jms_dm.dm_all_transfer_count_deatil_dt add columns( must_center_name string comment '应交件中心',
first_nodal_network_code string comment '始发集散code',
 first_nodal_network_name string comment '始发集散name') cascade

alter table jms_dm.dm_all_transfer_count_site_dt add columns( first_nodal_network_code string comment '始发集散code',first_nodal_network_name string comment '始发集散name') cascade

alter table jms_dm.dm_all_transfer_count_deatil_dt add columns(
 end_nodal_network_code string comment '目的集散code'
,end_nodal_network_name string comment '目的集散name'
,center_whole_route string comment '中转路由'
) cascade
